import random

from core.models import Rating, Restaurant, Sale, Staff, StaffRestaurant
from django.contrib.auth.models import User
from django.utils import timezone
from django.db.models import Avg, Value, CharField, Sum, Count
from django.db.models.functions import Lower, Concat


def run():
    # concatenation = Concat(
    #     'name', Value('[Rating: '), Avg('ratings__rating'), Value(']'),
    #     output_field=CharField()
    # )
    # restaurants = Restaurant.objects.annotate(message=concatenation)
    #
    # for r in restaurants:
    #     print(r.message)
    #
    # restaurants = Restaurant.objects.prefetch_related('sales').annotate(total_sales=Sum('sales__income'))
    # for r in restaurants:
    #     print(r.sales.all())
    #     print(r.total_sales)

    # print('=========')
    # restaurants = Restaurant.objects.annotate(num_ratings=Count('ratings'))
    # print(restaurants.values('name', 'num_ratings'))
    # for r in restaurants:
    #     print(r.num_ratings)
    #
    # print('------')
    # restaurants = Restaurant.objects.values('name').annotate(num_ratings=Count('ratings'))
    # # print(restaurants.values('name', 'num_ratings'))
    # for r in restaurants:
    #     print(r)

    print('||||||||||||||||||||||')
    restaurants = Restaurant.objects.all().annotate(total_sales=Sum('sales__income'))
    """
    SELECT AVG("__col1")
FROM (SELECT "core_sale"."income" AS "__col1"
      FROM "core_restaurant"
               LEFT OUTER JOIN "core_sale"
                               ON ("core_restaurant"."id" = "core_sale"."restaurant_id")
      GROUP BY "core_restaurant"."id", 1) subquery;

SELECT AVG("avg_sales")
FROM (SELECT "core_sale"."income" AS "avg_sales"
      FROM "core_restaurant"
               LEFT OUTER JOIN "core_sale"
                               ON ("core_restaurant"."id" = "core_sale"."restaurant_id")
      GROUP BY "core_restaurant"."id", 1) subquery;

-- SELECT "core_sale"."income" AS "income_s"
-- FROM "core_sale" RIGHT OUTER JOIN core_restaurant on (core_restaurant.id = core_sale.restaurant_id)
-- GROUP BY "core_sale"."restaurant_id";

SELECT AVG("income_s")
FROM (SELECT "core_sale"."income" AS "income_s"
      FROM "core_sale"
               RIGHT OUTER JOIN core_restaurant on (core_restaurant.id = core_sale.restaurant_id)
      GROUP BY "core_sale"."restaurant_id", 1) subquery;

SELECT AVG("total_sales")
FROM (SELECT SUM("core_sale"."income") AS "total_sales"
      FROM "core_restaurant"
               LEFT OUTER JOIN "core_sale" ON ("core_restaurant"."id" = "core_sale"."restaurant_id")
      GROUP BY "core_restaurant"."id") subquery;
    """
    print(restaurants.aggregate(avg_sales=Avg('sales__income')))
    print(restaurants.aggregate(avg_sales=Avg('total_sales')))

